  package com.enlorenz.common.excel.old;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import com.enlorenz.common.util.DataDetUtils;

public class Export2Excel {
	private String [] headTitles;
	private Integer [] headWidths;
	private Integer headWidth;
	
	public Export2Excel(String [] headTitles){
		this.headTitles=headTitles;
	}
	
	/**
	 * 创建内容样式
	 * @param workbook
	 * @return
	 */
	protected HSSFCellStyle createContextStyle(HSSFWorkbook workbook,HSSFFont font) {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setFillBackgroundColor(HSSFColor.WHITE.index);
		style.setFillForegroundColor(HSSFColor.WHITE.index);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFont(font);
		
		
		return style;
	}
	
	/**
	 * 创建主题样式
	 * @param workbook
	 * @return
	 */
	protected HSSFCellStyle createSubjectStyle(HSSFWorkbook workbook){
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //设置边框
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
		titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置
		HSSFFont titleFont = workbook.createFont();
		//		titleFont.setFontName("隶书");
		titleFont.setFontHeightInPoints((byte) 17);
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
		titleFont.setColor(HSSFColor.BLUE.index);
		titleStyle.setFont(titleFont);
		titleStyle.setWrapText(true);
		return titleStyle;
	}
	
	/**
	 * 创建描述样式
	 * @param workbook
	 * @return
	 */
	protected HSSFCellStyle createDescStyle(HSSFWorkbook workbook){
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //设置边框
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
		titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置
		HSSFFont titleFont = workbook.createFont();
		//		titleFont.setFontName("隶书");
		titleStyle.setFont(titleFont);
		titleStyle.setWrapText(true);
		return titleStyle;
	}
	
	/**
	 * 创建表头样式
	 * @param workbook
	 * @return
	 */
	protected HSSFCellStyle createHeadStyle(HSSFWorkbook workbook){
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //设置边框
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置
		HSSFFont titleFont = workbook.createFont();
		//		titleFont.setFontName("隶书");
		//titleFont.setFontHeightInPoints((byte) 14);
		//titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
		//titleFont.setColor(HSSFColor.BLUE.index);
		titleStyle.setFont(titleFont);
		titleStyle.setWrapText(true);
		return titleStyle;
	}
	
	
	private HSSFSheet sheet;
	private HSSFWorkbook workbook;
	
	public void init(){
		init("main");
	}
	
	public void init(String sheetName){
		workbook = new HSSFWorkbook();
		sheet = workbook.createSheet(sheetName);
		workbook.setPrintArea(0, //sheet index   
				0, //start column
				10 - 1, //end column  
				0,//config.getStartRow()-2, //start row 
				20 - 2 //end row
				); //设置打印区域
		if(!DataDetUtils.isArrEmpty(headWidths)){
			for(int i=0;i<headWidths.length;i++){
				sheet.setColumnWidth((short)i,headWidths[i].shortValue());
			}
		}else{
			for(int i=0;i<headTitles.length;i++){
				sheet.setColumnWidth((short)i,headWidth.shortValue());
			}
		}
		HSSFPrintSetup ps = sheet.getPrintSetup();
		sheet.setAutobreaks(true);
		ps.setFitHeight((short) 10000);
		ps.setFitWidth((short) 1);
		ps.setLandscape(true);//横向打印
	}
	
	/**
	 * 创建Sheet
	 * @param sheetName
	 */
	public void createSheet(String sheetName){
		rowIndex=0;
		sheet = workbook.createSheet(sheetName);
		if(!DataDetUtils.isArrEmpty(headWidths)){
			for(int i=0;i<headWidths.length;i++){
				sheet.setColumnWidth((short)i,headWidths[i].shortValue());
			}
		}else{
			for(int i=0;i<headTitles.length;i++){
				sheet.setColumnWidth((short)i,headWidth.shortValue());
			}
		}
		HSSFPrintSetup ps = sheet.getPrintSetup();
		sheet.setAutobreaks(true);
	}
	
	public void setFreezePane(int rowNum){
		sheet.createFreezePane(0, rowNum); //设置冻结区域
	}
	
	public void setSheetName(String name){
		workbook.setSheetName(0, name);
	}
	
	public void write2Stream(OutputStream os) throws IOException{
		workbook.write(os);
	}
	
	private int rowIndex=0;
	
	public void createTitle(String titleName){
		HSSFRow row = null;
		HSSFCell cell = null;
		int cIndex=rowIndex++;
		row = sheet.createRow((short)cIndex);
		row.setHeight((short) 600);
		//row.setRowStyle(createSubjectStyle(workbook));
		
		for(int i=0;i<headTitles.length;i++){   
			cell = row.createCell((short)i);   
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(createSubjectStyle(workbook));
			cell.setCellValue("");
		}
		cell = row.createCell(0);
		Region r=new Region();
		r.setColumnFrom((short)0);
		r.setColumnTo((short)headTitles.length);
		r.setRowFrom(cIndex);
		r.setRowTo(cIndex);
		
		sheet.addMergedRegion(r);
		
		
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(createSubjectStyle(workbook));
		cell.setCellValue(new HSSFRichTextString(titleName));
	}
	
	public void createDesc(String desc){
		HSSFRow row = null;
		HSSFCell cell = null;
		int cIndex=rowIndex++;
		row = sheet.createRow((short)cIndex);
		row.setHeight((short) 600);
		//row.setRowStyle(createSubjectStyle(workbook));
		
		for(int i=0;i<headTitles.length;i++){   
			cell = row.createCell((short)i);   
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(createDescStyle(workbook));
			cell.setCellValue("");
		}
		cell = row.createCell(0);
		Region r=new Region();
		r.setColumnFrom((short)0);
		r.setColumnTo((short)headTitles.length);
		r.setRowFrom(cIndex);
		r.setRowTo(cIndex);
		
		sheet.addMergedRegion(r);
		
		
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(createDescStyle(workbook));
		cell.setCellValue(new HSSFRichTextString(desc));
	}
	
	
	
	public void createHead(){
		HSSFRow row = null;
		HSSFCell cell = null;
		
		row = sheet.createRow((short) rowIndex++);
		row.setHeight((short) 300);
		//row.setRowStyle(createSubjectStyle(workbook));
		
		for(int i=0;i<headTitles.length;i++){   
			cell = row.createCell((short)i);   
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(new HSSFRichTextString(headTitles[i]));
		}
	}
	
	
	public void createContext(List list,DataHook dataHook){
		for(int i=0;i<list.size();i++){
			doObject(list.get(i), dataHook, i);
		}
		
	}
	
	public void createContext(Object [] arr,DataHook dataHook){
		for(int i=0;i<arr.length;i++){
			doObject(arr[i], dataHook, i);
		}
		
	}
	
	protected void doObject(Object object,DataHook dataHook,int i){
		HSSFRow row = sheet.createRow((short)rowIndex++);
		row.setHeight((short) 0x270);//(short)380 0x349
		HSSFFont font = workbook.createFont();
		for(int j=0;j<headTitles.length;j++){
			HSSFCell cell = row.createCell((short) j);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(dataHook.getData(object, i, j)));
			cell.setCellStyle(createContextStyle(workbook,font));
		}
	}
	
	/**
	 * 工作表的排版处理
	 * @param sheet
	 * @return
	 */
	public void doSheetMakeup(HSSFSheet sheet) {
		//设置页眉
		HSSFHeader header = sheet.getHeader();
		header.setRight("- " + HSSFFooter.page() + " -");
		//设置页脚
		HSSFFooter footer = sheet.getFooter();
		footer.setCenter("xykh");
		//设置打印时，显示网格
		sheet.setPrintGridlines(true);
	}
	
	public interface DataHook{
		public String getData(Object data,int row,int col);
	}

	public static void main(String[] args) {
		try {
			Export2Excel ex2=new Export2Excel(new String[]{"name1","name2"});
			ex2.setHeadWidth(5000);
			ex2.init();
			
			ex2.createTitle("Title");
			ex2.createDesc("201200210312 \r\n 21201020120 \r\n zhangsan");
			ex2.createHead();
			List list=new ArrayList();
			String [] arr=new String []{"cols1","cols11"};
			list.add(arr);
			arr=new String []{"cols2","cols22"};
			list.add(arr);
			
			ex2.createContext(list, new DataHook(){
				@Override
				public String getData(Object data, int row, int col) {
					String [] arr=(String [])data;
					return arr[col];
			}});
			
			ex2.setFreezePane(3);
			
			ex2.createSheet("2");
			ex2.createTitle("Title");
			ex2.createDesc("201200210312 \r\n 21201020120 \r\n zhangsan");
			ex2.createHead();
			list=new ArrayList();
			arr=new String []{"cols1","cols11"};
			list.add(arr);
			arr=new String []{"cols2","cols22"};
			list.add(arr);
			
			ex2.createContext(list, new DataHook(){
				@Override
				public String getData(Object data, int row, int col) {
					String [] arr=(String [])data;
					return arr[col];
			}});
			
			ex2.setFreezePane(3);
			
			ex2.write2Stream(new FileOutputStream("c:\\test.xls"));
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setHeadWidths(Integer[] headWidths) {
		this.headWidths = headWidths;
	}

	public void setHeadWidth(Integer headWidth) {
		this.headWidth = headWidth;
	}

	
}
